﻿using System;
using System.Collections;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Data.Common;
using System.Diagnostics;
using System.Linq;
using System.Net.NetworkInformation;
using System.Reflection;
using System.Text;
using Asion.Dapper.SqlBuilder;
using Asion.Dapper.SqlBuilder.Condition;
using Dapper;
using Microsoft.CSharp.RuntimeBinder;
using Microsoft.Data.SqlClient;
using static Asion.Dapper.SqlMapperExtensions;
using static Dapper.SqlMapper;

namespace Asion.Dapper
{
    /// <summary>
    /// SimpleCrud Extend
    /// </summary>
    public static partial class SqlMapperExtensions
    {
        private static ITableNameResolver _table_name_resolver = new SimpleTableNameResolver();

        private static IColumnNameResolver _column_name_resolver = new SimpleColumnNameResolver();



        static SqlMapperExtensions()
        {

        }


        #region insert

        public static int? Insert<TEntity>(this IDbConnection connection, TEntity entityToInsert, List<string> excludes = null, IDbTransaction transaction = null, int? commandTimeout = null) where TEntity : IEntity
        {
            return Insert<int?, TEntity>(connection, entityToInsert, excludes, transaction, commandTimeout);
        }


        public static TKey Insert<TKey, TEntity>(this IDbConnection connection, TEntity entityToInsert, List<string> excludes = null, IDbTransaction transaction = null, int? commandTimeout = null) where TEntity : IEntity
        {
            var baseType = typeof(TKey);
            //获取可空类型的基础类型
            var underlyingType = Nullable.GetUnderlyingType(baseType);
            var keytype = underlyingType ?? baseType;
            if (keytype != typeof(int) && keytype != typeof(uint) && keytype != typeof(long) && keytype != typeof(ulong) && keytype != typeof(short) && keytype != typeof(ushort) && keytype != typeof(Guid) && keytype != typeof(string))
            {
                throw new Exception("Invalid return type");
            }
            var builder = SimpleCrudSqlBuilderFactory.Create<TEntity>();
            var sql = builder.GetInsertSql(entityToInsert, true, excludes: excludes, isReturnId: true);
            var r = connection.Query(sql, entityToInsert, transaction, true, commandTimeout);
            return (TKey)r.First().id;
        }



        public static int BatchInsert<TEntity>(this IDbConnection connection, List<TEntity> entityToInsert, List<string> excludeColumns = null, IDbTransaction transaction = null, int? commandTimeout = null) where TEntity : IEntity
        {
            //当TEntity是一个接口的时候
            var builder = SimpleCrudSqlBuilderFactory.Create<TEntity>();
            var sql = builder.GetInsertSql(entityToInsert, false, excludes: excludeColumns);
            //
            var r = connection.Execute(sql, entityToInsert, transaction, commandTimeout);
            return r;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public static void BatchInserBySqlBulkCopy<TEntity>(this IDbConnection connection, IList<TEntity> entityToInsert) where TEntity : IEntity
        {
            DataTable table = new DataTable();

            var modelBaseType = typeof(TEntity);
            //get ids
            var tableInfo = GetPropertiesCache(new PropertyIdentity(modelBaseType));

            //get insert columns
            var columns = tableInfo.Columns.Where(a => a.IsSupportInsert).ToList();

            foreach (var property in columns)
            {
                //set the column count in this list
                table.Columns.Add(property.ColumnName, Nullable.GetUnderlyingType(property.ColumnType) ?? property.ColumnType);
            }

            foreach (var element in entityToInsert)
            {
                table.Rows.Add(element);
            }

            SqlBulkCopyHelper.BatchInsert((SqlConnection)connection, tableInfo.TableName, table);
        }


        #endregion

        #region delete
        public static int Delete<TEntity>(this IDbConnection connection, IWhere where, IDbTransaction transaction = null, int? commandTimeout = null) where TEntity : IEntity
        {
            var builder = SimpleCrudSqlBuilderFactory.Create<TEntity>();
            var sql = builder.GetDeleteSql(where);
            var para = where as IConditionDynamicParameters;
            return connection.Execute(sql, para.GetDynamicParameters(), transaction, commandTimeout);
        }






        public static int DeleteList<TEntity>(this IDbConnection connection, IWhere where, IDbTransaction transaction = null, int? commandTimeout = null) where TEntity : IEntity
        {
            var builder = SimpleCrudSqlBuilderFactory.Create<TEntity>();
            var sql = builder.GetDeleteSql(where);
            var para = where as IConditionDynamicParameters;
            return connection.Execute(sql, para.GetDynamicParameters(), transaction, commandTimeout);
        }
        #endregion

        #region update



        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="connection"></param>
        /// <param name="entityToUpdate"></param>
        /// <param name="where">这个where 不带参数参数值都在entityToUpdate中</param>
        /// <param name="transaction"></param>
        /// <param name="commandTimeout"></param>
        /// <returns></returns>
        public static int Update<TEntity>(this IDbConnection connection, TEntity entityToUpdate, IUpdateWhere where, List<string> excludes = null, IDbTransaction transaction = null, int? commandTimeout = null) where TEntity : IEntity
        {
            var builder = SimpleCrudSqlBuilderFactory.Create<TEntity>();
            var sql = builder.GetUpdateSql<TEntity>(entityToUpdate, where, excludes: excludes);
            return connection.Execute(sql, entityToUpdate, transaction, commandTimeout);
        }


        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="connection"></param>
        /// <param name="entityToUpdate"></param>
        /// <param name="where">这个where 不带参数参数值都在entityToUpdate中</param>
        /// <param name="transaction"></param>
        /// <param name="commandTimeout"></param>
        /// <returns></returns>
        public static int BatchUpdate<TEntity>(this IDbConnection connection, List<TEntity> models, string[] updateColmuns, string updateColmunsWhere, IWhere where, IDbTransaction transaction = null, int? commandTimeout = null) where TEntity : class, IEntity
        {
            var builder = SimpleCrudSqlBuilderFactory.Create<TEntity>();
            var update = new BatchUpdateColumn(models, updateColmuns, updateColmunsWhere);
            var sql = builder.GetBatchUpdateSql<TEntity>(update, where);
            var updateCondition = update as IConditionDynamicParameters;
            var whereCondition = where as IConditionDynamicParameters;

            var para = new DynamicParameters();
            para.AddDynamicParams(updateCondition.GetDynamicParameters());
            para.AddDynamicParams(whereCondition.GetDynamicParameters());


            return connection.Execute(sql, para, transaction, commandTimeout);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="connection"></param>
        /// <param name="entityToUpdate">中包含参数</param>
        /// <param name="where">包含参数值</param>
        /// <param name="transaction"></param>
        /// <param name="commandTimeout"></param>
        /// <returns></returns>
        public static int Update<TEntity>(this IDbConnection connection, IUpdateColumn entityToUpdate, IWhere where, IDbTransaction transaction = null, int? commandTimeout = null) where TEntity : IEntity
        {
            var builder = SimpleCrudSqlBuilderFactory.Create<TEntity>();
            var sql = builder.GetUpdateSql<TEntity>(entityToUpdate, where);
            var wherePara = where as IConditionDynamicParameters;
            var entityParam = entityToUpdate as IConditionDynamicParameters;
            var para = new DynamicParameters();
            para.AddDynamicParams(entityParam.GetDynamicParameters());
            para.AddDynamicParams(wherePara.GetDynamicParameters());
            return connection.Execute(sql, para, transaction, commandTimeout);
        }


        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="connection"></param>
        /// <param name="entity">中包含参数</param>
        /// <param name="where">包含参数值</param>
        /// <param name="transaction"></param>
        /// <param name="commandTimeout"></param>
        /// <returns></returns>
        public static SaveReponse Save<TEntity>(this IDbConnection connection, TEntity entity, IUpdateWhere where, SaveExcludeRequest excludeRequest, IDbTransaction transaction = null, int? commandTimeout = null) where TEntity : IEntity
        {
            var result = SaveTypeEnum.Insert;
            var exist = Exist<TEntity>(connection, entity, where, transaction, commandTimeout);
            if (!exist)
            {
                Insert<TEntity>(connection, entity, excludes: excludeRequest.InsertExclude, transaction: transaction, commandTimeout: commandTimeout);
            }
            else
            {
                Update<TEntity>(connection, entity, where, excludes: excludeRequest.UpdateExclude, transaction: transaction, commandTimeout: commandTimeout);
                result = SaveTypeEnum.Update;
            }
            return new SaveReponse(result);
        }




        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="connection"></param>
        /// <param name="entity">中包含参数</param>
        /// <param name="where">包含参数值</param>
        /// <param name="transaction"></param>
        /// <param name="commandTimeout"></param>
        /// <returns></returns>
        public static BatchSaveReponse BatchSave<TEntity>(this IDbConnection connection, BatchSaveRequest<TEntity> request, IDbTransaction transaction = null, int? commandTimeout = null) where TEntity : class, IEntity
        {


            var info = SqlMapperExtensions.GetPropertiesCache(new PropertyIdentity(typeof(TEntity)));
            var custom_table_mappinghandle = new SimpleCustomTableMappingHandle(info);
            var mapping = custom_table_mappinghandle.GetColumnMapping(request.UpdateColmunsWhere);

            var list = GetList<TEntity>(connection, new SelectColumn(mapping.DataColumnName), request.UpdateWhere);

            //将list 放到字典中
            HashSet<object> hashSet = new HashSet<object>();
            foreach (var item in list)
            {
                var type = item.GetType();
                var property = type.GetProperty(mapping.ColumnName);
                var value = property.GetValue(item);
                if (!hashSet.Contains(value))
                {
                    hashSet.Add(value);
                }

            }

            //将新增和更新的实体区分出来
            var insertEntity = new List<TEntity>();
            var updateEntity = new List<TEntity>();
            foreach (var item in request.Entity)
            {

                var type = item.GetType();
                var property = type.GetProperty(mapping.ColumnName);
                var value = property.GetValue(item);
                if (hashSet.Contains(value))
                {
                    updateEntity.Add(item);
                }
                else
                {
                    insertEntity.Add(item);
                }
            }
            int insertSuccessCont = 0;
            int updateSuccessCont = 0;
            // 这里有个问题如果UpdateWhere 中的code条件大于updateEntity中的code 就可能会有数据被跟新，因为UpdateWhere中的code是包换了insert中的code的，所以这里调用了一下update和insert执行的位置，是update的时候 code还没有才不会出问题
            if (updateEntity.Any())
            {
                updateSuccessCont = BatchUpdate<TEntity>(connection, updateEntity, request.UpdateColmuns.ToArray(), request.UpdateColmunsWhere, request.UpdateWhere, transaction: transaction, commandTimeout: commandTimeout);
            }
            if (insertEntity.Any())
            {

                insertSuccessCont = BatchInsert<TEntity>(connection, insertEntity, excludeColumns: request.InsertExclude, transaction: transaction, commandTimeout: commandTimeout);

            }

            return new BatchSaveReponse()
            {
                AllCount = request.Entity.Count(),
                InsertCount = insertEntity.Count(),
                InsertSuccessCount = insertSuccessCont,
                UpdateCount = updateEntity.Count(),
                UpdateSuccessCount = updateSuccessCont

            };
        }





        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="connection"></param>
        /// <param name="entityToUpdate"></param>
        /// <param name="where">这个where 不带参数参数值都在entityToUpdate中</param>
        /// <param name="transaction"></param>
        /// <param name="commandTimeout"></param>
        /// <returns></returns>
        public static bool Exist<TEntity>(this IDbConnection connection, TEntity entityToUpdate, IUpdateWhere where, IDbTransaction transaction = null, int? commandTimeout = null) where TEntity : IEntity
        {
            var builder = SimpleCrudSqlBuilderFactory.Create<TEntity>();
            var sql = builder.GetSelectExistSql(where);
            return connection.ExecuteScalar<bool>(sql, entityToUpdate, transaction, commandTimeout);
        }
        /// <summary>
        /// 减少数量
        /// </summary>
        /// <returns></returns>
        public static int DecreaseCount<TEntity>(this IDbConnection connection, string column, int value, IWhere where, IDbTransaction transaction = null, int? commandTimeout = null) where TEntity : IEntity
        {
            var builder = SimpleCrudSqlBuilderFactory.Create<TEntity>();
            var sql = builder.GetUpdateCountSql<TEntity>(column, UpdateCountType.Decrease, value, where);
            var para = where as IConditionDynamicParameters;
            return connection.Execute(sql, para.GetDynamicParameters(), transaction, commandTimeout);
        }

        /// <summary>
        /// 增加数量
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="connection"></param>
        /// <param name="column"></param>
        /// <param name="value"></param>
        /// <param name="where"></param>
        /// <param name="transaction"></param>
        /// <param name="commandTimeout"></param>
        /// <returns></returns>
        public static int IncreaseCount<TEntity>(this IDbConnection connection, string column, int value, IWhere where, IDbTransaction transaction = null, int? commandTimeout = null) where TEntity : IEntity
        {
            var builder = SimpleCrudSqlBuilderFactory.Create<TEntity>();
            var sql = builder.GetUpdateCountSql<TEntity>(column, UpdateCountType.Increase, value, where);
            var para = where as IConditionDynamicParameters;
            return connection.Execute(sql, para.GetDynamicParameters(), transaction, commandTimeout);
        }




        #endregion

        #region select
        public static TEntity Get<TEntity>(this IDbConnection connection, IWhere where, IOrderBy order = null, IDbTransaction transaction = null, int? commandTimeout = null) where TEntity : IEntity
        {
            var currenttype = typeof(TEntity);
            //get select sql
            var builder = SimpleCrudSqlBuilderFactory.Create<TEntity>();
            var sql = builder.GetSelectSql(new AllSelectColumn(), where, order,topCount:1);
            var para = where as IConditionDynamicParameters;
            return connection.Query<TEntity>(sql, para.GetDynamicParameters(), transaction, true, commandTimeout).FirstOrDefault();
        }

        public static TEntity Get<TEntity>(this IDbConnection connection, ISelectColumn column, IWhere where, IOrderBy order = null, IDbTransaction transaction = null, int? commandTimeout = null) where TEntity : IEntity
        {
            var currenttype = typeof(TEntity);
            //get select sql
            var builder = SimpleCrudSqlBuilderFactory.Create<TEntity>();
            var sql = builder.GetSelectSql(column, where, order, topCount: 1);
            var para = where as IConditionDynamicParameters;
            return connection.Query<TEntity>(sql, para.GetDynamicParameters(), transaction, true, commandTimeout).FirstOrDefault();
        }


        public static IEnumerable<TEntity> GetList<TEntity>(this IDbConnection connection, IWhere where, IOrderBy order = null, IGroupColumn group = null, IDbTransaction transaction = null, int? commandTimeout = null) where TEntity : IEntity
        {
            var currenttype = typeof(TEntity);
            //get select sql
            var builder = SimpleCrudSqlBuilderFactory.Create<TEntity>();
            var sql = builder.GetSelectSql(new AllSelectColumn(), where, order, groupBy: group);
            var para = where as IConditionDynamicParameters;
            return connection.Query<TEntity>(sql, para.GetDynamicParameters(), transaction, true, commandTimeout);
        }

        public static IEnumerable<TEntity> GetList<TEntity>(this IDbConnection connection, ISelectColumn column, IWhere where, IOrderBy order = null, IGroupColumn group = null, IDbTransaction transaction = null, int? commandTimeout = null) where TEntity : IEntity
        {
            var currenttype = typeof(TEntity);
            //get select sql
            var builder = SimpleCrudSqlBuilderFactory.Create<TEntity>();
            var sql = builder.GetSelectSql(column, where, order, groupBy: group);
            var para = where as IConditionDynamicParameters;
            return connection.Query<TEntity>(sql, para.GetDynamicParameters(), transaction, true, commandTimeout);
        }

        public static IEnumerable<TEntity> GetTopList<TEntity>(this IDbConnection connection, int topCount, ISelectColumn column, IWhere where, IOrderBy order = null, IGroupColumn group = null, IDbTransaction transaction = null, int? commandTimeout = null) where TEntity : IEntity
        {
            var currenttype = typeof(TEntity);
            //get select sql
            var builder = SimpleCrudSqlBuilderFactory.Create<TEntity>();
            var sql = builder.GetSelectSql(column, where, order, topCount: topCount, groupBy: group);
            var para = where as IConditionDynamicParameters;
            return connection.Query<TEntity>(sql, para.GetDynamicParameters(), transaction, true, commandTimeout);
        }


        public static PageReponse<TEntity> GetPageList<TEntity>(this IDbConnection connection, ISelectColumn column, IWhere where, int pageNumber = 1, int rowsPerPage = DapperConfiguration.default_rows_page, IOrderBy order = null, IDbTransaction transaction = null, int? commandTimeout = null) where TEntity : IEntity
        {


            pageNumber = pageNumber <= 0 ? 1 : pageNumber;
            rowsPerPage = rowsPerPage <= 0 ? DapperConfiguration.default_rows_page : rowsPerPage;
            //get select sql
            var builder = SimpleCrudSqlBuilderFactory.Create<TEntity>();
            var sql = builder.GetSelectPagedSql(column, where, pageNumber: pageNumber, rowsPerPage: rowsPerPage, order: order);
            var para = where as IConditionDynamicParameters;
            using (var gird = connection.QueryMultiple(sql, para.GetDynamicParameters(), transaction, commandTimeout))
            {
                var resultA = gird.Read<TEntity>();
                var resultB = gird.ReadFirst<int>();
                var page = new PageReponse<TEntity>(pageNumber, rowsPerPage, resultB, resultA);
                return page;
            }


        }
        #endregion



        public static int RecordCount<TEntity>(this IDbConnection connection, IWhere where, IDbTransaction transaction = null, int? commandTimeout = null) where TEntity : IEntity
        {
            var builder = SimpleCrudSqlBuilderFactory.Create<TEntity>();
            var sql = builder.GetSelectCountSql(where);
            var para = where as IConditionDynamicParameters;
            return connection.ExecuteScalar<int>(sql, para.GetDynamicParameters(), transaction, commandTimeout);
        }



        public static int Exist<TEntity>(this IDbConnection connection, IWhere where, IDbTransaction transaction = null, int? commandTimeout = null) where TEntity : IEntity
        {

            var builder = SimpleCrudSqlBuilderFactory.Create<TEntity>();
            var sql = builder.GetSelectExistSql(where);
            var para = where as IConditionDynamicParameters;
            return connection.ExecuteScalar<int>(sql.ToString(), para.GetDynamicParameters(), transaction, commandTimeout);
        }

        /*   /// <summary>
           /// Maps a query to objects
           /// </summary>
           /// <typeparam name="TFirst">The first type in the record set</typeparam>
           /// <typeparam name="TSecond">The second type in the record set</typeparam>
           /// <typeparam name="TReturn">The return type</typeparam>
           /// <param name="cnn"></param>
           /// <param name="sql"></param>
           /// <param name="map"></param>
           /// <param name="param"></param>
           /// <param name="transaction"></param>
           /// <param name="buffered"></param>
           /// <param name="splitOn">The Field we should split and read the second object from (default: id)</param>
           /// <param name="commandTimeout">Number of seconds before command execution timeout</param>
           /// <param name="commandType">Is it a stored proc or a batch?</param>
           /// <returns></returns>
           public static IEnumerable<TReturn> Query<TFirst, TSecond, TReturn>(
               this IDbConnection connection, string sql, Func<TFirst, TSecond, TReturn> map, object param = null, IDbTransaction transaction = null, bool buffered = true, string splitOn = "Id", int? commandTimeout = null, CommandType? commandType = null
           )
           {
               return connection.Query<TFirst, TSecond, TReturn>(sql, map, param, transaction, buffered, splitOn, commandTimeout, commandType);
           }*/
    }
}
